package com.stylefeng.guns.core.util;
import java.awt.Color;
import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * （导出表格）
 * @author guxinghong
 *
 */

public class ExportExcelUtil {
	
	//带统计数据导出表格
	public static String exportExcelWithCountData(String title, List<String> nameStr, List<List<String>> userList,String countData) {
		String url = "";

		try {
			String path = Thread.currentThread().getContextClassLoader().getResource("").getPath();
        	int num=path.indexOf("target");
        	path=path.substring(1,num).replace('/', '\\')+"src\\main\\webapp\\static\\temporaryFolder";
			String name = "\\table\\" + new Date().getTime() + ".xls";
			url = name.replace(path, "");
			int rowId = 0;
			String file = "\\table";
			System.out.println(path+ file);
			File dFile = new File(path+ file);
			if(!dFile.exists()){
				dFile.mkdirs();
			}
			
			WritableWorkbook wbook = jxl.Workbook.createWorkbook(new File(path + name));
			WritableSheet wsheet = null;
			WritableFont titleFont =  new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
			
			if(title == null || "".equals(title)) {
				
				wsheet = wbook.createSheet("wbook", 0);
			} else {
				title="佛山市机关事务管理局"+title;
				System.out.println(title);
				wsheet = wbook.createSheet(title, 0);
//				wsheet.setPageSetup(PageOrientation.LANDSCAPE,PaperSize.A3,1d,1d);//设置excel打印格式(纵横方向、用纸大小、double)
//				wsheet.getSettings().setScaleFactor(50); //打印预览缩小为50%
				wsheet.setPageSetup(PageOrientation.LANDSCAPE,PaperSize.A4,1d,1d);//设置excel打印格式(纵横方向、用纸大小、double)
				wsheet.getSettings().setFitWidth(1);//将所有列打印在一页
				WritableFont wfont = new WritableFont(WritableFont.createFont("宋体"), 18, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
				WritableCellFormat wcfFC = new WritableCellFormat(wfont);//标题样式
				wcfFC.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM, Colour.GRAY_50);
				wcfFC.setBackground(Colour.WHITE);  //单元格背景
				wcfFC.setAlignment(Alignment.CENTRE);  //单元格左右居中
				wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); //单元格中的内容垂直方向居中				
				wsheet.addCell(new Label(0, rowId, title, wcfFC));
				wsheet.mergeCells(0, rowId, nameStr.size()-1, 0);
				wsheet.setRowView(rowId,600); //行高
				rowId++;
			}
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");
				String exportTime=sdf.format(new Date())+"  "+countData;//导出时间
				WritableFont timefont = new WritableFont(WritableFont.createFont("宋体"), 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
				WritableCellFormat timeFormat = new WritableCellFormat(timefont);//导出时间样式
				timeFormat.setBackground(Colour.WHITE);  //单元格背景
				timeFormat.setAlignment(Alignment.CENTRE);  //单元格左右居中
				timeFormat.setVerticalAlignment(VerticalAlignment.CENTRE); //单元格中的内容垂直方向居中
				timeFormat.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM, Colour.GRAY_50);
				wsheet.addCell(new Label(0,1,exportTime,timeFormat));//列、行、内容、样式
				wsheet.mergeCells(0, rowId, nameStr.size()-1, 1);
				wsheet.setRowView(rowId,600); //行高
				rowId++;
			//初始化列头
				//根据系统中各表内容大概设置前6列宽度
				wsheet.setColumnView(0, 15);
				wsheet.setColumnView(1, 15);
				wsheet.setColumnView(2, 15);
				wsheet.setColumnView(3, 15);
				wsheet.setColumnView(4, 15);
				wsheet.setColumnView(5, 15);
			for (int i = 0; i < nameStr.size(); i++) {
				WritableCellFormat tempTitleFormat = new WritableCellFormat(titleFont);//表头样式
				tempTitleFormat.setBackground(Colour.GRAY_25);
				tempTitleFormat.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM, Colour.GRAY_50);
				tempTitleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				wsheet.addCell(new Label(i, rowId, nameStr.get(i),tempTitleFormat));
//				wsheet.setColumnView(i,30); //设置excel的每一列的宽度								
				if(i>5){
					wsheet.setColumnView(i,15);//6列后各列宽度
				}
			}
			wsheet.setRowView(rowId,885); //行高
			
				//自定义颜色
					Color color = Color.decode("#e0e0e0"); 
					wbook.setColourRGB(Colour.AQUA, color.getRed(),
		                    color.getGreen(), color.getBlue());
				//初始化每列字符串的字符数为0
				int[] max= new int[30];
				for (int i = 0; i < 30; i++) {
					max[i]=0;
				}
			for (int i = 0; i < userList.size(); i++) {			
				WritableCellFormat titleFormat = new WritableCellFormat( titleFont);//表格内容样式
				titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				titleFormat.setBorder(jxl.format.Border.ALL,BorderLineStyle.MEDIUM, Colour.GRAY_25);			
				List<String> strList = userList.get(i);
				if((i+rowId+2)%2==1){
					titleFormat.setBackground(Colour.AQUA);
				}else{
					titleFormat.setBackground(Colour.WHITE);
				}
				for (int j = 0; j < strList.size(); j++) {
					//合理设置列宽
					int  strLength=0;
					if(strList.get(j)!=null){
						strLength=strList.get(j).length();
					}
					for (int k = 0; k < strList.size(); k++) {
						if(j==k){
							//第一列
							if(max[j]<=strLength){
								max[j]=strLength;
							}
						}
						
					}
					
					for (int k = 0; k < strList.size(); k++) {
						if(max[k]>4 && max[k] <7){
							wsheet.setColumnView(k,24);
						}
						if(max[k]>=7 && max[k] <=10){
							wsheet.setColumnView(k,28);
						}
						if(max[k]>10 && max[k] <=12){
							wsheet.setColumnView(k,38);
						}
						if(max[k]>12 && max[k] <=14){
							wsheet.setColumnView(k,40);
						}
						if(max[k]>14 && max[k] <=18){
							wsheet.setColumnView(k,50);
						}
						if(max[k]>18 && max[k] <=25){
							wsheet.setColumnView(k,64);
						}
						if(max[k]>25 && max[k] <=40){
							wsheet.setColumnView(k,70);
						}
						if(max[k]>40 && max[k] <=60){
							wsheet.setColumnView(k,90);
						}
						if(max[k]>60){
							wsheet.setColumnView(k,110);
						}
						
					}
					String str = strList.get(j);
					wsheet.addCell(new Label(j, rowId + 1 + i, str,titleFormat));
				}
				wsheet.setRowView(rowId + 1 + i,600); //行高
			}
			wsheet.setRowView(0,1000); //单元格行高
			wbook.write();
			wbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		url.replace("\\", "/");

		return url;
	}
}
